import os, django
import gc

os.environ.setdefault("DJANGO_SETTINGS_MODULE", "api_automation_test.settings")
django.setup()

import xlrd
from backend.models import *
from backend.common.common_db import Interface, UpdataInterface
from backend.common.api_response import JsonResponse
from rest_framework.views import APIView
from rest_framework.authentication import TokenAuthentication


# 去掉获取到的参数小数点后面的0
def rm_zero(row_data):
    integer = row_data
    # 将获取到的excel中数字后面的小数及零去掉
    if integer != '':
        integer = int(row_data)
    return integer


# excel字段为空判断
def not_null_judge(field, fieldlist, line):
    if field == '':
        fieldlist.append(line)
    return fieldlist


# excel满足某条件时字段为空判断
def condition_not_null_judge(conditionfield, field, fieldlist, line):
    if conditionfield == 1:
        if field == '':
            fieldlist.append(line)
    return fieldlist


# 判断依赖名称是否存在
def condition_relation_name_exist(header_relation, field, fieldlist, line):
    if header_relation == 1:
        lyzdinterface_name = Lyzd_Interface.objects.values('id').filter(interface_name_zh__exact=field)
        if lyzdinterface_name.count() == 0:
            fieldlist.append(line)
    return fieldlist

#判断Headerkey为Content-Type时，HeaderkeyValue存在
def condition_HeaderkeyValue(header_key,header_key_value,fieldlist,line):
    # 创建数组存放ContentType的类型
    ContentType=['application/json','application/x-www-form-urlencoded','multipart/form-data','text/plain']
    #判断当headerkey为Content-Type时，headerkeyvalue是否存在
    if header_key == 'Content-Type':
        if header_key_value in ContentType:
            pass
        else:
            fieldlist.append(line)
    return fieldlist


# 将header依赖名称（relation_interface_name）转化为依赖id（relation_interface_id）
def header_relation_name_to_id(row_data, Project_id):
    relation_interface_name = row_data
    relation_interface_id = Lyzd_Interface.objects.values('id').filter(interface_name_zh__exact=relation_interface_name,
                                                                       project_id__exact=Project_id)
    if relation_interface_id.count() != 0:
        relation_interface_id = relation_interface_id[0]['id']
    else:
        relation_interface_id = ''
    return relation_interface_id


# 将param依赖名称（relation_interface_name）转化为依赖id（relation_interface_id）
def param_relation_name_to_id(row_data, Model_id):
    relation_interface_name = row_data
    relation_interface_id = Lyzd_Interface.objects.values('id').filter(
        interface_name_zh__exact=relation_interface_name, model_id__exact=Model_id)
    if relation_interface_id.count() != 0:
        relation_interface_id = relation_interface_id[0]['id']
    else:
        relation_interface_id = ''
    return relation_interface_id


# 检查结果方法
def check_result(list, result_list, describe):
    if len(list) != 0:
        for i in range(0, len(list)):
            result_list.append("第" + str(list[i] + 1) + describe)
    return result_list




# excel导入文件校验
def excel_check(x):

    try:
        book = xlrd.open_workbook(file_contents=x, on_demand=True)
        print(88)
    except Exception as e:
        print(e)



    sheets = book.sheet_names()  # 获取所有sheet表名

    project_name_list = []  # 存放所属项目名称为空的列表
    model_name_list = []  # 存放所属模块名称为空的列表
    project_id_list = []  # 存放所属项目名称不存在的列表
    model_id_list = []  # 存放所属模块名称不存在的列表
    interface_name_zh_list = []  # 存放接口名称为空的列表
    interface_name_en_list = []  # 存放方法名称为空的列表
    encryption_list = []  # 接口加密时，存放加密url为空的列表
    decrypt_list = []  # 接口加密时，存放解密url为空的列表
    HeaderkeyValue_list = []
    header_relation_name_list = []  # 接口headerkey有依赖时，存放依赖名称为空的列表
    header_relation_key_list = []  # 接口headerkey有依赖时，存放依赖key为空的列表
    param_relation_name_list = []  # 接口paramkey有依赖时，存放依赖名称为空的列表
    param_relation_key_list = []  # 接口paramkey有依赖时，存放依赖key为空的列表
    header_relation_name_list2 = []  # 接口headerkey有依赖时，存放依赖名称不存在的列表
    param_relation_name_list2 = []  # 接口paramkey有依赖时，存放依赖名称不存在的列表
    check_result_list = []  # 存放check结果

    for sheet in sheets:
        sh = book.sheet_by_name(sheet)  # 打开每一张表
        row_num = sh.nrows

        for i in range(1, row_num):  # 第一行是标题名，对应表中的字段名所以应该从第二行开始，计算机以0开始计数，所以值是1
            row_data = sh.row_values(i)  # 按行获取excel的值

            sysmodel = Sys_Model.objects.all()
            sysproject = Sys_Project.objects.all()
            # 根据项目名称获取项目id
            Project_id = ''
            for a in sysproject:
                if (row_data[0] == a.project_name.strip()):
                    Project_id = a.id

            # 根据模块名称获取模块id
            Model_id = ''
            for a in sysmodel:
                if (row_data[1] == a.model_name.strip()):
                    Model_id = a.id

            # 将excel中的字段赋值给变量
            project_name = row_data[0]
            model_name = row_data[1]
            project_id = Project_id
            model_id = Model_id
            interface_name_zh = row_data[2]
            interface_name_en = row_data[3]
            # requestType = row_data[4]
            authentication = rm_zero(row_data[5])
            encryption = row_data[6]
            decrypt = row_data[7]
            header_key = row_data[8]
            # header_param_type= row_data[9]
            header_key_value = row_data[10]
            header_relation = rm_zero(row_data[11])
            header_relation_name = row_data[12]
            header_relation_key = row_data[13]
            # param_key = row_data[14]
            # param_param_type = row_data[15]
            param_relation = rm_zero(row_data[16])
            param_relation_name = row_data[17]
            param_relation_key = row_data[18]
            # inParamShow = row_data[19]
            # outParamShow = row_data[20]
            # create_user = ''
            # content = ''

            # 判断excel必填项是否为空
            interface_name_zhlist = not_null_judge(interface_name_zh, interface_name_zh_list, i)
            interface_name_enlist = not_null_judge(interface_name_en, interface_name_en_list, i)
            # project_namelist = not_null_judge(project_name, project_name_list, i)
            # project_idlist = not_null_judge(project_id, project_id_list, i)
            # model_namelist = not_null_judge(model_name, model_name_list, i)
            # model_idlist = not_null_judge(model_id, model_id_list, i)

            # 判断项目名称、模块名称是否为空，不为空时项目名称、模块名称id是否存在
            if project_name == '':
                not_null_judge(project_name, project_name_list, i)
            else:
                not_null_judge(project_id, project_id_list, i)
            if model_name == '':
                not_null_judge(model_name, model_name_list, i)
            else:
                not_null_judge(model_id, model_id_list, i)

            # 加密方式为1时，加密url，解密url不可以为空，存在依赖接口时，依赖名称、依赖key不可以为空
            encryptionlist = condition_not_null_judge(authentication, encryption, encryption_list, i)
            decryptlist = condition_not_null_judge(authentication, decrypt, decrypt_list, i)
            header_relation_namelist = condition_not_null_judge(header_relation, header_relation_name,
                                                                header_relation_name_list, i)
            header_relation_keylist = condition_not_null_judge(header_relation, header_relation_key,
                                                               header_relation_key_list, i)
            param_relation_namelist = condition_not_null_judge(param_relation, param_relation_name,
                                                               param_relation_name_list, i)
            param_relation_keylist = condition_not_null_judge(param_relation, param_relation_key,
                                                              param_relation_key_list, i)

            # 接口存在依赖时，判断依赖名称是否存在
            header_relation_namelist2 = condition_relation_name_exist(header_relation, header_relation_name,
                                                                      header_relation_name_list2, i)
            param_relation_namelist2 = condition_relation_name_exist(param_relation, param_relation_name,
                                                                     param_relation_name_list2, i)


            #创建数组存放ContentType的类型
            # ContentType=['application/json','application/x-www-form-urlencoded','multipart/form-data','text/plain']
            # #判断当headerkey为Content-Type时，headerkeyvalue是否存在
            # if header_key == 'Content-Type':
            #     if header_key in ContentType:
            #         pass
            #     else:
            #         check_result_list.append('第'+i+'行Headerkey为Content-Type时，HeaderkeyValue不存在')
            #
            # print(check_result_list)

    # if len(projectlist) != 0:
    #     for i in range(0,len(projectlist)):
    #         check_result_list.append("第"+str(projectlist[i])+"行项目名称不可以为空")
            HeaderkeyValuelist = condition_HeaderkeyValue(header_key,header_key_value, HeaderkeyValue_list, i)
    check_result(HeaderkeyValuelist,check_result_list,'行Headerkey为Content-Type时，HeaderkeyValue不存在')
    check_result(project_name_list, check_result_list, "行项目名称不可以为空")
    check_result(project_id_list, check_result_list, "行项目名称不存在")
    check_result(model_name_list, check_result_list, "行模块名称不可以为空")
    check_result(model_id_list, check_result_list, "行模块名称不存在")
    check_result(interface_name_zhlist, check_result_list, "行接口名称不可以为空")
    check_result(interface_name_enlist, check_result_list, "行方法名称不可以为空")
    check_result(encryptionlist, check_result_list, "行是否加密为1加密URL不可以为空,")
    check_result(decryptlist, check_result_list, "行是否加密为1解密URL不可以为空")
    check_result(header_relation_namelist, check_result_list, "行header是否依赖为1依赖名称不可以为空")
    check_result(header_relation_keylist, check_result_list, "行header是否依赖为1依赖key不可以为空")
    check_result(param_relation_namelist, check_result_list, "行param是否依赖为1依赖名称不可以为空")
    check_result(param_relation_keylist, check_result_list, "行param是否依赖为1依赖key不可以为空")
    check_result(header_relation_namelist2, check_result_list, "行header是否依赖为1依赖名称不存在")
    check_result(param_relation_namelist2, check_result_list, "行param是否依赖为1依赖名称不存在")
    return check_result_list


# excel 文件导入
def store_to(x):

    book = xlrd.open_workbook(file_contents=x, on_demand=True)
    # book = xlrd.open_workbook(excel_file);  # 打开excel文件
    sheets = book.sheet_names()  # 获取所有sheet表名

    # 创建数组存放ContentType的类型
    ContentType=['application/json','application/x-www-form-urlencoded','multipart/form-data','text/plain']


    for sheet in sheets:
        sh = book.sheet_by_name(sheet)  # 打开每一张表
        row_num = sh.nrows
        for i in range(1, row_num):  # 第一行是标题名，对应表中的字段名所以应该从第二行开始，计算机以0开始计数，所以值是1
            row_data = sh.row_values(i)  # 按行获取excel的值

            sysmodel = Sys_Model.objects.all()
            sysproject = Sys_Project.objects.all()
            # 根据项目名称获取项目id
            Project_id = ''
            for a in sysproject:
                if (row_data[0] == a.project_name.strip()):
                    Project_id = a.id

            # 根据模块名称获取模块id
            Model_id = ''
            for a in sysmodel:
                if (row_data[1] == a.model_name.strip()):
                    Model_id = a.id

            # 将excel中的字段赋值给变量
            project_id = Project_id
            model_id = Model_id
            interface_name_zh = row_data[2]
            interface_name_en = row_data[3]
            requestType = row_data[4]
            authentication = rm_zero(row_data[5])
            encryption = row_data[6]
            decrypt = row_data[7]
            header_key = row_data[8]
            header_param_type = row_data[9]
            header_key_value= row_data[10]
            header_relation = rm_zero(row_data[11])
            header_relation_name = row_data[12]
            header_relation_key = row_data[13]
            param_key = row_data[14]
            param_param_type = row_data[15]
            param_relation = rm_zero(row_data[16])
            param_relation_name = row_data[17]
            param_relation_key = row_data[18]
            inParamShow = row_data[19]
            outParamShow = row_data[20]
            create_user = ''
            content = ''

            print(8888)

            # 当本行数据的校验不通过时则不插入本行，开始执行插入下一行数据
            if project_id == '' or model_id == '' or interface_name_zh == '' or interface_name_en == '':
                continue
            if authentication == 1:
                if encryption == '' or decrypt == '':
                    continue
            if header_relation == 1:
                if header_relation_name == '' or header_relation_key == '':
                    continue
                else:
                    lyzdinterface_name1 = Lyzd_Interface.objects.values('id').filter(
                        interface_name_zh__exact=header_relation_name)
                    if lyzdinterface_name1.count() == 0:
                        continue
            if param_relation == 1:
                if param_relation_name == '' or param_relation_key == '':
                    continue
                else:
                    lyzdinterface_name2 = Lyzd_Interface.objects.values('id').filter(
                        interface_name_zh__exact=param_relation_name)
                    if lyzdinterface_name2.count() == 0:
                        continue
            # 判断当headerkey为Content-Type时，headerkeyvalue是否存在
            if header_key == 'Content-Type':
                if header_key_value in ContentType:
                    pass
                else:
                    continue

            # 判断该接口名称是否已存在，同一个项目下接口名称不可以重复（interface_name_zh，model_id相同时则说明该接口已存在）
            lyzdinterface_id = Lyzd_Interface.objects.values('id').filter(interface_name_zh__exact=row_data[2],
                                                                          project_id__exact=project_id)
            print(lyzdinterface_id, lyzdinterface_id.count())
            # 接口名称已存在
            print(99999999)
            if lyzdinterface_id.count() != 0:
                print("接口名称已存在")
                # 判断加密方式为否时，加密url、解密url插入空数据
                if authentication != 1:
                    encryption = ''
                    decrypt = ''
                    print("url不加密，执行更新语句")
                    UpdataInterface.UpdateInterface(interface_name_zh, project_id, interface_name_en, requestType,
                                                    authentication, encryption,
                                                    decrypt, inParamShow, outParamShow, content, model_id)
                else:
                    print("url加密，执行更新语句")
                    UpdataInterface.UpdateInterface(interface_name_zh, project_id, interface_name_en, requestType,
                                                    authentication, encryption,
                                                    decrypt, inParamShow, outParamShow, content, model_id)
                # 获取interface_id
                interface_id = lyzdinterface_id[0]['id']
                print(interface_id)
                # 判断header_key是否已存在，若存在则对lyzd_interface_header表进行更新操作，若不存在则进行插入操作
                lyzdheader_id = Lyzd_Interface_Header.objects.values('id').filter(interface_id__exact=interface_id,
                                                                                  header_key__exact=header_key)
                print(lyzdheader_id)
                # header_key存在执行更新操作
                if lyzdheader_id.count() != 0:
                    if header_relation != 1:
                        header_relation_id = ''
                        header_relation_key = ''
                        print("没有依赖接口，执行更新语句")
                        #判断参数是否为Content-Type
                        if header_key=='Content-Type':
                            header_param_type='String'
                            header_relation = 0
                            UpdataInterface.UpdateInterfaceHeader(header_key,header_key_value,header_param_type, interface_id, header_relation, header_relation_id,
                                  header_relation_key)
                        else:
                            UpdataInterface.UpdateInterfaceHeader(header_key, header_key_value, header_param_type,
                                                                  interface_id, header_relation, header_relation_id,
                                                                  header_relation_key)
                    else:
                        header_relation_id = header_relation_name_to_id(header_relation_name, project_id)
                        print("有依赖接口，执行更新语句")
                        if header_key == 'Content-Type':
                            header_param_type = 'String'
                            header_relation=0
                            header_relation_id = ''
                            header_relation_key = ''
                            UpdataInterface.UpdateInterfaceHeader(header_key, header_key_value, header_param_type,
                                                                   interface_id, header_relation, header_relation_id,
                                                                   header_relation_key)
                        else:
                            UpdataInterface.UpdateInterfaceHeader(header_key, header_key_value, header_param_type,
                                                                  interface_id, header_relation, header_relation_id,
                                                                  header_relation_key)
                # header_key不存在执行插入操作
                else:
                    if header_relation != 1:
                        header_relation_id = ''
                        header_relation_key = ''
                        # 判断参数是否为Content-Type,当参数为Content-Type时，header_param_type为String,header_relation=0,header_relation_id='',header_relation_key=''
                        if header_key == 'Content-Type':
                            header_param_type = 'String'
                            header_relation = 0
                            Interface.addInterfaceHeader(header_key,header_key_value,header_param_type, interface_id, header_relation,
                                                         header_relation_id, header_relation_key)
                        else:
                            Interface.addInterfaceHeader(header_key, header_key_value, header_param_type, interface_id,
                                                         header_relation,
                                                         header_relation_id, header_relation_key)
                    else:
                        header_relation_id = header_relation_name_to_id(header_relation_name, project_id)
                        if header_key == 'Content-Type':
                            header_param_type = 'String'
                            header_relation=0
                            header_relation_id = ''
                            header_relation_key = ''
                            Interface.addInterfaceHeader(header_key,header_key_value,header_param_type, interface_id, header_relation,
                                                         header_relation_id, header_relation_key)
                        else:
                            Interface.addInterfaceHeader(header_key, header_key_value, header_param_type, interface_id,
                                                         header_relation,
                                                         header_relation_id, header_relation_key)

                # 判断param_key是否已存在，若存在则对lyzd_interface_param表进行更新操作，若不存在则进行插入操作
                lyzdparam_id = Lyzd_Interface_Param.objects.values('id').filter(interface_id__exact=interface_id,
                                                                                param_key__exact=param_key)
                print(lyzdparam_id)
                # param存在执行更新操作
                if lyzdparam_id.count() != 0:
                    if param_relation != 1:
                        param_relation_id = ''
                        param_relation_key = ''
                        print("没有依赖接口，执行更新语句")
                        UpdataInterface.UpdateInterfaceParam(param_key, interface_id, param_param_type, param_relation,
                                                             param_relation_id,
                                                             param_relation_key)

                    else:
                        param_relation_id = param_relation_name_to_id(param_relation_name, model_id)
                        print("没有依赖接口，执行更新语句")
                        UpdataInterface.UpdateInterfaceParam(param_key, interface_id, param_param_type, param_relation,
                                                             param_relation_id,
                                                             param_relation_key)
                # param不存在执行插入操作
                else:
                    if param_relation != 1:
                        param_relation_id = ''
                        param_relation_key = ''
                        Interface.addInterfaceParam(param_key, param_param_type, interface_id, param_relation,
                                                    param_relation_id, param_relation_key)
                    else:
                        param_relation_id = param_relation_name_to_id(param_relation_name, model_id)
                        Interface.addInterfaceParam(param_key, param_param_type, interface_id, param_relation,
                                                    param_relation_id, param_relation_key)
            # 接口名称不存在
            else:
                print("接口名称不存在")
                # 判断加密方式为否时，加密url、解密url插入空数据
                if authentication != 1:
                    encryption = ''
                    decrypt = ''
                    print("url不加密，执行插入语句")
                    Interface.addInterface(interface_name_zh, interface_name_en, requestType, encryption, decrypt,
                                           authentication
                                           , create_user, inParamShow, outParamShow, content, project_id, model_id)
                else:
                    print("url加密，执行插入语句")
                    Interface.addInterface(interface_name_zh, interface_name_en, requestType, encryption, decrypt,
                                           authentication
                                           , create_user, inParamShow, outParamShow, content, project_id, model_id)
                # 获取最新的interface_id
                interface_id = Lyzd_Interface.objects.values('id').last()['id']
                print(interface_id)
                # Lyzd_Interface_Header表执行插入操作
                if header_relation != 1:
                    header_relation_id = ''
                    header_relation_key = ''
                    print("没有依赖接口，执行插入语句")
                    if header_key == 'Content-Type':
                        header_relation = 0
                        header_param_type = 'String'
                        Interface.addInterfaceHeader(header_key, header_key_value,header_param_type, interface_id, header_relation,
                                                 header_relation_id, header_relation_key)
                    else:
                        Interface.addInterfaceHeader(header_key, header_key_value, header_param_type, interface_id,
                                                     header_relation,
                                                     header_relation_id, header_relation_key)
                else:
                    header_relation_id = header_relation_name_to_id(header_relation_name, project_id)
                    print("有依赖接口，执行插入语句")
                    if header_key == 'Content-Type':
                        header_param_type = 'String'
                        header_relation = 0
                        header_relation_id = ''
                        header_relation_key = ''
                        Interface.addInterfaceHeader(header_key, header_key_value, header_param_type, interface_id,
                                                 header_relation,
                                                 header_relation_id, header_relation_key)
                    else:
                        Interface.addInterfaceHeader(header_key, header_key_value, header_param_type, interface_id,
                                                     header_relation,
                                                     header_relation_id, header_relation_key)
                # Lyzd_Interface_Param表执行插入操作
                if param_relation != 1:
                    param_relation_id = ''
                    param_relation_key = ''
                    print("没有依赖接口，执行插入语句")
                    Interface.addInterfaceParam(param_key, param_param_type, interface_id, param_relation,
                                                param_relation_id, param_relation_key)
                else:
                    param_relation_id = param_relation_name_to_id(param_relation_name, model_id)
                    print("有依赖接口，执行插入语句")
                    Interface.addInterfaceParam(param_key, param_param_type, interface_id, param_relation,
                                                param_relation_id, param_relation_key)
            print(777777777777777)
    print(88888888888888881)
    # book.unload_sheet()
    # xlrd.dump(file_contents=excel_file)



class InterfaceExcleImport(APIView):
    authentication_classes = (TokenAuthentication,)
    permission_classes = ()

    def post(self, request):

        try:

            p_file_list = request.FILES.getlist("file", None)

            aa = p_file_list[0]
            bb = p_file_list[0]

            x = p_file_list[0].read()
            store_to(x)

            check_resultlist = excel_check(x)  # 解析检查文件内容bing
            # check_resultlist=excel_check("导入模板1(1).xlsx")#解析检查文件内容bing
            print(check_resultlist)
            print(check_resultlist.__len__())
            if check_resultlist.__len__() != 0:#校验报错
                return JsonResponse(code="999997", data=check_resultlist, msg="校验失败")
            else: #正常
                return JsonResponse(code="999999", msg="导入成功")
        except Exception as e:
            return JsonResponse(code="999998", msg=e)


if __name__ == '__main__':
    # store_to(".xlsx")

    # excel_check("导入模板1(1).xlsx")
    header_key='token'
    header_key_value='aaa'
    header_param_type='String'
    interface_id=292
    relation=0
    header_relation_id=''
    header_relation_key=''
    UpdataInterface.UpdateInterfaceHeader(header_key, header_key_value, header_param_type, interface_id, relation,
                                          header_relation_id,
                                          header_relation_key)
